package com.fc.control;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;

import com.fc.db.DBConnect;
import com.fc.model.Coach;
import com.fc.util.Pagination;

public class CoachControl {

public static Pagination pageInstance = null;
	
	//add coach method
	public static Boolean addCoach(Coach coach){
		DBConnect dbc = new DBConnect();
		String sql = "insert into coach (store_id,wages,type,price,addtime,position,idcard,experience,name) " +
				"value(?,?,?,?,?,?,?,?,?) ";
		dbc.prepareStatement(sql);
		try{
			dbc.setInt(1,coach.getStore_id());
			dbc.setInt(2,coach.getWages());
			dbc.setString(3,coach.getType());
			dbc.setInt(4,coach.getPrice());
			dbc.setString(5, coach.getAddtime());
			dbc.setString(6,coach.getPosition());
			dbc.setString(7, coach.getIdcard());
			dbc.setInt(8,coach.getExperience());
			dbc.setString(9, coach.getName());
			dbc.executeUpdate();
			return true;
		}catch(SQLException e){
			e.printStackTrace();
			return false;
		}finally{
			dbc.close();
		}
	}
	//test add method
	//public static void main(String[] args){
		//Coach coach = new Coach();
		//coach.setAddtime("2012-2-1");
		//coach.setExperience(2);
		//coach.setIdcard("222");
		//coach.setName("name");
		//coach.setPosition("职位");
		//coach.setPrice(200);
		//coach.setType("私人教练");
		//coach.setStore_id(1);
		//coach.setWages(1000);
		//CoachControl.addCoach(coach);
	//}
	//remove worker method
	public static Boolean deleteCoach(int id){
		DBConnect dbc = new DBConnect();
		String sql = "delete from coach where id=?";
		dbc.prepareStatement(sql);
		try{
			dbc.setInt(1,id);
			dbc.executeUpdate();
			return true;
		}catch(SQLException e){
			e.printStackTrace();
			return false;
		}finally{
			dbc.close();
		}
	}
	//update coach method
	public static Boolean updateCoach(Coach coach,int id){
		DBConnect dbc = new DBConnect();
		String sql = "update coach set position=?,wages=?,price=?,experience=? where id=?";
		dbc.prepareStatement(sql);
		try{
			dbc.setString(1, coach.getPosition());
			dbc.setInt(2,coach.getWages());
			dbc.setInt(3,coach.getPrice());
			dbc.setInt(4, coach.getExperience());
			dbc.setInt(5,id);
			dbc.executeUpdate();
			return true;
		}catch(SQLException e){
			e.printStackTrace();
			return false;
		}finally{
			dbc.close();
			
		}
	}
	//get coach information by id
	public static Coach getCoachInfo(int id){
		DBConnect dbc = new DBConnect();
		String sql = "select * from coach where id=?";
		dbc.prepareStatement(sql);
		try{
			Coach coach = new Coach();
			dbc.setInt(1,id);
			ResultSet rs = dbc.executeQuery();
		if(rs.next())
		{
			coach.setStore_id(rs.getInt("store_id"));
			coach.setWages(rs.getInt("wages"));
			coach.setAddtime(rs.getString("addtime"));
			coach.setIdcard(rs.getString("idcard"));
			coach.setPosition(rs.getString("position"));
			coach.setPrice(rs.getInt("price"));
			coach.setExperience(rs.getInt("experience"));
			coach.setCoach_time(rs.getInt("coach_time"));
			coach.setName(rs.getString("name"));
			coach.setType(rs.getString("type"));
			//System.out.println("Yes");
			return coach;
		}
	}catch(SQLException e){
		e.printStackTrace();
		return null;
	}finally{
		dbc.close();
	}
	return null;
	}
	
	//get coach information by idcard
	/*public static Coach getCoachInfo1(String idcard){
		DBConnect dbc = new DBConnect();
		String sql = "select * from coach where idcard=?";
		dbc.prepareStatement(sql);
		try{
			Coach coach = new Coach();
			dbc.setString(1,idcard);
			ResultSet rs = dbc.executeQuery();
		if(rs.next())
		{
			dbc.setInt(1, coach.getStore_id());
			dbc.setInt(2, coach.getWages());
			dbc.setString(3,coach.getAddtime());
			dbc.setInt(4, coach.getId());
			dbc.setString(5, coach.getPosition());
			dbc.setInt(6,coach.getCoach_time());
			dbc.setInt(7,coach.getExperience());
			dbc.setInt(8,coach.getPrice());
			return coach;
		}
	}catch(SQLException e){
		e.printStackTrace();
		return null;
	}finally{
		dbc.close();
	}
	return null;
	}*/
	
	//pagination method
	public Collection getCoach(int page,int rowsPerPage)throws SQLException,Exception{
		String sql = "select * from coach";
		pageInstance = new CoachControlPage(sql,rowsPerPage);
		try{
			return pageInstance.getPage(page);
		}catch(SQLException e){
			e.printStackTrace();
		}catch(Exception e){
			e.printStackTrace();
		}
		return null;
	}
}

class CoachControlPage extends Pagination{
	public CoachControlPage(String sql,int rowsPerPage){
		super(sql,rowsPerPage,1);
	}
	@Override
	protected Collection<Coach> packResultSet(ResultSet rs) throws SQLException {
		Collection<Coach> coachList = new ArrayList<Coach>();
		while (rs.next()) {
			Coach coach=new Coach();
			coach.setName(rs.getString("name"));
			coach.setType(rs.getString("type"));
			coach.setPosition(rs.getString("position"));
			coach.setPrice(rs.getInt("price"));
			coach.setWages(rs.getInt("wages"));
			coach.setExperience(rs.getInt("experience"));
			coach.setId(rs.getInt("id"));
			//coach.setCoach_time(rs.getInt("coach_time"));
			coachList.add(coach);
			coach = null;
		}
		return coachList;
	}
	
}
